﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SQLite;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using DE.FoxSolutions.WinPlexEditor.Properties;

namespace DE.FoxSolutions.WinPlexEditor
{
    public partial class FormMain : Form
    {
        public FormMain()
        {
            InitializeComponent();         

            this.btnSearchDatabase.Text = "...";
            this.btnSearchExcelExport.Text = "...";
            this.comboBoxLanguage.Text = "Englisch";
            InitializeLanguage();
            validateBtn();

        }

        private void InitializeLanguage()
        {
            if (this.comboBoxLanguage.Text == "Deutsch")
            {
                Thread.CurrentThread.CurrentUICulture = CultureInfo.GetCultureInfo("de-de");
            }
            else
            {
                Thread.CurrentThread.CurrentUICulture = CultureInfo.GetCultureInfo("en-us");
            }
            
            this.lbldatabase.Text = strings.lblpathdatabase;
            this.lblexcelexport.Text = strings.lblpathexceleport;
            this.btnexport.Text = strings.btnexport;
            this.lblDisclaimer.Text = strings.lblDisclaimer;
            this.lblPfad.Text = @"C:\Users\XXX\AppData\Local\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db";
        }

        private void comboBoxLanguage_TextChanged(object sender, EventArgs e)
        {
            InitializeLanguage();
        }

        private void btnSearchDatabase_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Multiselect = false;
            ofd.Filter = "Plex|*.db";
            DialogResult dr = ofd.ShowDialog();            
            if (dr != DialogResult.OK)
            {
                return;
            }
            this.textBoxdatabase.Text = ofd.FileName;
            validateBtn();

        }

        private void btnSearchExcelExport_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            DialogResult dr = fbd.ShowDialog();
            if (dr != DialogResult.OK)
            {
                return;
            }

            this.textBoxExcelFile.Text = fbd.SelectedPath;
            validateBtn();

        }
        private void validateBtn()
        {
            if (this.textBoxdatabase.TextLength > 0 && this.textBoxExcelFile.TextLength > 0)
            {
                this.btnexport.Enabled = true;
            }
            else
            {
                this.btnexport.Enabled = false;
            }
        }

        private void btnexport_Click(object sender, EventArgs e)
        {
            List<string> Namen = new List<string>();
            try
            {

                using (SQLiteConnection connection = new SQLiteConnection())
                {
                    connection.ConnectionString = "Data Source=" + this.textBoxdatabase.Text;
                    connection.Open();
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = "SELECT [title] FROM metadata_items";

                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {                           
                            int i = 0;
                            while (reader.Read())
                            {
                                Namen.Add(reader[0].ToString());
                            }

                            reader.Close();
                        }
                        if (Namen.Count == 0)
                        {
                            MessageBox.Show(strings.NoItemsFound);
                            return;
                        }

                    }
                }


                if (File.Exists(this.textBoxExcelFile.Text + @"\Plex.xls"))
                {
                    if (MessageBox.Show(strings.FileExistsOverwrite,strings.FileExists,MessageBoxButtons.YesNo) == DialogResult.No ) return;
                    File.Delete(this.textBoxExcelFile.Text + @"\Plex.xls");
                }

                using (OleDbConnection xlsconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.textBoxExcelFile.Text + @"\Plex.xls; Extended Properties=""Excel 8.0;HDR=YES"""))
                {
                    xlsconn.Open();
                    OleDbCommand cmd = xlsconn.CreateCommand();
                    cmd.CommandText = "CREATE TABLE [Movie] (name char(255))";
                    cmd.ExecuteNonQuery();

                    foreach (string name in Namen)
                    {
                        cmd.CommandText = "INSERT INTO [Movie] (name) VALUES('" + name.Replace("'","").Replace(@"""","") + "')";
                        cmd.ExecuteNonQuery();
                    }
                    xlsconn.Close();
                    MessageBox.Show("Finished", "Finished");
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }

    }
}
